package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.fiap.trabalho.entity.Category;

public class JDBCCategoryDAO implements CategoryDAO {
	private Connection cn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	private boolean abreConexao() throws Exception{
		try{
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); 
			String url = "jdbc:derby:simpleDB;create=true";
			cn = DriverManager.getConnection(url,"TrabalhoPersistenciaJava","TrabalhoPersistenciaJava");
			return true;
		}
		catch(Exception ex){
			throw ex;
		}
	}
	
	//Tempo : 20 minutos
	public Category createCategory(Category category) {
		try {
			abreConexao();
			cn.setAutoCommit(false);
			
			ps = cn.prepareStatement("INSERT INTO CATEGORY (NAME) VALUES (?)", 
					Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, category.getName());
			ps.executeUpdate();
			
			ResultSet rs = ps.getGeneratedKeys();
			if(rs.next()) 
			{
				category.setId( rs.getInt(1));
			}

			cn.commit();
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return category;
	}
	
	//Tempo : 20 minutos
	public boolean deleteCategory(Category category) {
		boolean valida = false;
		
		try {
			abreConexao();
			
			cn.setAutoCommit(false);

			ps = cn.prepareStatement("DELETE FROM MOVIE_CATEGORY WHERE ID_CATEGORY = ?");
			ps.setInt(1, category.getId());
			ps.executeUpdate();
			
			ps = cn.prepareStatement("DELETE FROM CATEGORY WHERE ID_CATEGORY = ? ");
			ps.setInt(1, category.getId());
			ps.executeUpdate();
			
			cn.commit();
			
			valida = true;
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		finally{
			try {
				cn.setAutoCommit(true);
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return valida;
	}
	
	//Tempo : 15 minutos
	public List<Category> selectCategoryByName(String name) {
		List<Category> listCategory = new ArrayList<Category>();
		
		try {
			abreConexao();
			ps = cn.prepareStatement("SELECT ID_CATEGORY, NAME FROM CATEGORY WHERE NAME = ?");
			ps.setString(1, name);
			rs = ps.executeQuery();
			
			Category category;
			while (rs.next()) {
				category = new Category();
				category.setId(rs.getInt(1));
				category.setName(rs.getString(2));
				listCategory.add(category);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				ps.close();
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return listCategory;
	}
}
